﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
//using System.Data.Odbc;//.SqlClient;
using Com.Becom.CommonLibrary.Common;
using System.Reflection;
using MySql.Data.MySqlClient;

using System.Data.Common;

namespace Com.Becom.CommonLibrary.Db.AdoNet
{

    /// <summary>
    /// 数据库交互抽象类的SQLServer实现。
    /// </summary>
    public class SqlServerDb : AbstractDb
    {

        /// <summary>
        /// 获取数据库连接字符串。
        /// </summary>
        /// <returns>数据库连接字符串</returns>
        public override string GetDbConnectionString()
        {
            string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
            return connectionString;
        }

        /// <summary>
        /// 获取数据库连接。
        /// </summary>
        /// <returns>数据库连接</returns>
        public override IDbConnection GetDbConnection()
        {
            MySqlConnection sqlConnection = new MySqlConnection(GetDbConnectionString());
            return sqlConnection;
        }
        public override IDbConnection GetDbConnection(string connectionStr)
        {
            MySqlConnection sqlConnection = new MySqlConnection(connectionStr);
            return sqlConnection;
        }

        /// <summary>
        /// 获取数据库适配器。
        /// </summary>
        /// <returns>数据库适配器</returns>
        public override IDbDataAdapter GetDbDataAdapter()
        {
            MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
            return sqlDataAdapter;
        }

        /// <summary>
        /// 获取数据库命令行。
        /// </summary>
        /// <returns>数据库命令行</returns>
        public override IDbCommand GetDbCommand()
        {
            MySqlCommand sqlCommand = new MySqlCommand();
            MySqlConnection sqlConnection = (MySqlConnection)GetDbConnection();
            sqlCommand.Connection = sqlConnection;
            return sqlCommand;
        }
        public override IDbCommand GetDbCommand(string connStr)
        {
            MySqlCommand sqlCommand = new MySqlCommand();
            MySqlConnection sqlConnection = (MySqlConnection)GetDbConnection(connStr);
            sqlCommand.Connection = sqlConnection;
            return sqlCommand;
        }
        /// <summary>
        /// 向数据库命令行里追加参数。
        /// </summary>
        /// <param name="SQLCommand">数据库命令行</param>
        /// <param name="dbtype">字段类型</param>
        /// <param name="ParamName">参数名称</param>
        /// <param name="ParamValue">参数值</param>
        /// <returns>添加参数后的数据库命令行</returns>
        public override IDbCommand AddParam(IDbCommand SQLCommand, DbType dbtype, string ParamName, object ParamValue)
        {
            IDbDataParameter iDbDataParameter = SQLCommand.CreateParameter();
            iDbDataParameter.DbType = dbtype;
            iDbDataParameter.ParameterName = ParamName;
            iDbDataParameter.Value = ParamValue;
            SQLCommand.Parameters.Add(iDbDataParameter);
            return SQLCommand;
        }

        /// <summary>
        /// 获取数据集。
        /// </summary>
        /// <param name="SQLCommand">数据查询SQL语句命令行。</param>
        /// <returns>查询结果数据集</returns>
        public override DataSet GetDataSet(IDbCommand SQLCommand)
        {
            MySqlCommand sqlCommand = (MySqlCommand)SQLCommand;
            MySqlDataAdapter sqlDataAdapter = (MySqlDataAdapter)GetDbDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            DataSet dataSet = new DataSet();
            dataSet.EnforceConstraints = false;
            sqlDataAdapter.FillSchema(dataSet, SchemaType.Mapped);
            sqlDataAdapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 获取数据集。
        /// </summary>
        /// <param name="SQL">数据查询SQL语句。</param>
        /// <returns>查询结果数据集</returns>
        public override DataSet GetDataSet(string SQL)
        {
            MySqlCommand sqlCommand = (MySqlCommand)GetDbCommand();
            sqlCommand.CommandText = SQL;
            DataSet dataSet = GetDataSet(sqlCommand);
            return dataSet;
        }

        /// <summary>
        /// 执行数据库命令行。
        /// </summary>
        /// <param name="SQLCommand">数据库命令行</param>
        /// <returns>成功执行影响的记录数</returns>
        public override int RunSQL(IDbCommand SQLCommand)
        {
            try
            {
                if (SQLCommand != null && SQLCommand.Connection != null && SQLCommand.Connection.State != ConnectionState.Open)
                {
                    SQLCommand.Connection.Open();
                }
                int resultNum = SQLCommand.ExecuteNonQuery();
                return resultNum;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (SQLCommand != null && SQLCommand.Connection != null && SQLCommand.Connection.State != ConnectionState.Closed)
                {
                    SQLCommand.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL更新语句。
        /// </summary>
        /// <param name="SQL">SQL更新语句</param>
        /// <returns>成功执行影响的记录数</returns>
        public override int RunSQL(string SQL)
        {
            MySqlCommand sqlCommand = (MySqlCommand)GetDbCommand();
            sqlCommand.CommandText = SQL;
            return RunSQL(sqlCommand);
        }

        /// <summary>
        /// 把数据集更新到数据库里。免SQL语句。
        /// </summary>
        /// <param name="dataSet">待入库的数据集</param>
        /// <param name="tablename">目标表名称</param>
        /// <returns>成功执行影响的记录数</returns>
        public override int UpdateDataSetToDb(DataSet dataSet, string Tablename)
        {
            dataSet.Tables[0].TableName = Tablename;
            MySqlCommand sqlCommand = (MySqlCommand)GetDbCommand();
            sqlCommand.CommandText = string.Format("select * from {0} where 1=0", Tablename);
            MySqlDataAdapter sqlDataAdapter = (MySqlDataAdapter)GetDbDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            MySqlCommandBuilder sqlCommandBuilder = new MySqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
            if (dataSet.Tables[0].PrimaryKey != null && dataSet.Tables[0].PrimaryKey.Length > 0)
            {
                sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
                sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
            }
            return sqlDataAdapter.Update(dataSet);
        }

        public override DbParameter[] GetDbParameter(object[] cmdParamName, object[] cmdParamValue)
        {
            try
            {
                if (cmdParamName.Length != cmdParamValue.Length)
                {
                    throw new Exception("参数名与参数值个数不一致");
                }
                MySqlParameter[] param = new MySqlParameter[cmdParamName.Length];
                for (int i = 0; i < cmdParamName.Length; i++)
                {
                    MySqlParameter para = new MySqlParameter(cmdParamName[i].ToString(), cmdParamValue[i]);
                    param[i] = para;
                }
                return param;
            }
            catch (Exception ex)
            {
                AppLog.WriteLog(ex.ToString(), true, MethodInfo.GetCurrentMethod());
                throw ex;
            }
        }
        public override DbParameter[] GetDbParameter(object cmdParamName, object cmdParamValue)
        {
            try
            {
                MySqlParameter[] param = new MySqlParameter[1];
                MySqlParameter para = new MySqlParameter(cmdParamName.ToString(), cmdParamValue);
                param[0] = para;
                return param;
            }
            catch (Exception ex)
            {
                AppLog.WriteLog(ex.ToString(), true, MethodInfo.GetCurrentMethod());
                throw ex;
            }
        }
        public override DbParameter GetDbParameter(object cmdParamName, object cmdParamValue, ParameterDirection paramDirection)
        {
            try
            {
                MySqlParameter para = new MySqlParameter(cmdParamName.ToString(), cmdParamValue);
                para.Direction = paramDirection;
                return para;
            }
            catch (Exception ex)
            {
                AppLog.WriteLog(ex.ToString(), true, MethodInfo.GetCurrentMethod());
                throw ex;
            }
        }

    }

}
